<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class M_Billing_form Extends MY_Model
{
	private $last_insert_id = '';
	
	private $b_h_t = 'student_billing_header';
	private $b_i_t = 'student_billing_items';
	public function __construct()
	{
		parent::__construct();
	}
	
	public function create_billing_form($data,$enrollment_id)
	{
		//prep array to object
		$data = (object)$data;
		
		//prep data for student billing form header table
		$for_student_header['current'] = $data->current;
		$for_student_header['date'] = $this->format_date($data->date_generated);
		$for_student_header['due_date'] = $this->format_date($data->due_date);
		$for_student_header['stmt_period'] = $data->stmy_period;
		$for_student_header['days_due_thirty'] = $data->thirty_days;
		$for_student_header['days_due_sixty'] = $data->sixty_days;
		$for_student_header['days_due_ninety'] = $data->ninety_days;
		$for_student_header['over_ninety_days'] = $data->overninety_days;
		$for_student_header['description'] = $data->message;
		$for_student_header['enrollment_id'] = $enrollment_id;
		$for_student_header['sy_id'] = $this->c_sy->id;
		$for_student_header['description'] = $data->message;
		$for_student_header['created'] = NOW;

		
		//insert heading
		$q = $this->db->insert($this->b_h_t,$for_student_header);
		$this->last_insert_id = $this->db->insert_id();
		if($this->db->affected_rows() >= 1)
		{
			//prep data for student billing form item table
			if($data->items)
			{
				$from_post = $data->items;
				foreach($from_post['date'] as $k => $v)
				{
					$qty = $from_post['qty'][$k] == '' ?  1 : $from_post['qty'][$k];
					$for_student_items[$k]['description'] = $from_post['desc'][$k];
					$for_student_items[$k]['date'] = $this->format_date($v);
					$for_student_items[$k]['amount'] = $from_post['price'][$k] * $qty;
					$for_student_items[$k]['created'] = NOW;
					$for_student_items[$k]['sbh_id'] = $this->last_insert_id;
					$for_student_items[$k]['ay_id'] = $this->c_sy->id;
					$for_student_items[$k]['qty'] = $qty;
					$for_student_items[$k]['original_amount'] = $from_post['price'][$k];
					
				}
			}

			// pd($for_student_items);
			
			//insert batch
			$this->db->insert_batch($this->b_i_t,$for_student_items);
			
			if($this->db->affected_rows() >= 1)
			{
				$stat['stat'] = TRUE;
				$stat['log'] = 'Billing Form Successfully created.';	
			}else{
				$stat['stat'] = FALSE;
				$stat['log'] = 'Unable to insert Billing Items';
			}
			return (object)$stat;
		}else{
			$stat['stat'] = FALSE;
			$stat['log'] = 'Unable to insert Billing Header';
			return (object)$stat;
		}
	}
	
	public function get_student_billings($id,$items = FALSE)
	{
		$this->db->select('id,active,spr_id,
						   current,
						   DATE_FORMAT(date,"%M %d ,%Y") as date,
						   DATE_FORMAT(created,"%M %d, %Y - %h:%i:%s %p") as created,
						   stmt_period,
						   DATE_FORMAT(due_date,"%M %d ,%Y") as due_date,
						   days_due_thirty as ddt,
						   days_due_sixty as dds,
						   days_due_ninety as ddn,
						   over_ninety_days as ond,
						   description,
						   ',FALSE)
				 ->where('enrollment_id',$id)
				 ->where('is_deleted',0)
				 ->order_by('created','DESC');
		$q = $this->db->get($this->b_h_t);
		if($q->num_rows() >= 1)
		{
			$data = $q->result();
			
			if($items == FALSE)
			{
				foreach($data as $k => $v)
				{
					$billing[$k]['id'] = $v->id;
					$billing[$k]['id_hashed'] = $this->hs->encrypt(intval($v->id));
					$billing[$k]['current'] = $v->current;
					$billing[$k]['date'] = $v->date;
					$billing[$k]['stmt_period'] = $v->stmt_period;
					$billing[$k]['due'] = $v->due_date;
					$billing[$k]['thirty'] = $v->ddt;
					$billing[$k]['sixty'] = $v->dds;
					$billing[$k]['ninety'] = $v->ddn;
					$billing[$k]['o_ninety'] = $v->ond;
					$billing[$k]['desc'] = $v->description;
					$billing[$k]['balance'] = $this->_get_items_balance($v->id);
					$billing[$k]['is_active'] = $v->active;
					$billing[$k]['is_paid'] = $v->spr_id;
					$billing[$k]['created'] = $v->created;
				}
			}else{
				foreach($data as $k => $v)
				{
					$billing[$k]['id'] = $v->id;
					$billing[$k]['id_hashed'] = $this->hs->encrypt(intval($v->id));
					$billing[$k]['current'] = $v->current;
					$billing[$k]['date'] = $v->date;
					$billing[$k]['stmt_period'] = $v->stmt_period;
					$billing[$k]['due'] = $v->due_date;
					$billing[$k]['thirty'] = $v->ddt;
					$billing[$k]['sixty'] = $v->dds;
					$billing[$k]['ninety'] = $v->ddn;
					$billing[$k]['o_ninety'] = $v->ond;
					$billing[$k]['desc'] = $v->description;
					$billing[$k]['balance'] = $this->_get_items_balance($v->id);
					$billing[$k]['items'] = $this->get_student_billings_items($v->id);
					$billing[$k]['created'] = $v->created;
				}			
			}

			return $billing;
		}else{
			return FALSE;
		}
	}
	
	public function get_specific_bill($e_id,$b_id)
	{
		$this->db->select('id,
						   current,
						   DATE_FORMAT(date,"%b %e ,%Y") as date,
						   stmt_period,
						   DATE_FORMAT(due_date,"%b %e ,%Y") as due_date,
						   days_due_thirty as ddt,
						   days_due_sixty as dds,
						   days_due_ninety as ddn,
						   over_ninety_days as ond,
						   description,
						   ',FALSE)
				 ->where('enrollment_id',$e_id)
				 ->where('id',$b_id)
				 ->where('sy_id',$this->c_sy->id)
				 ->where('is_deleted',0)
				 ->limit(1);
		$q = $this->db->get($this->b_h_t);

		if($q->num_rows() >= 1)
		{
			$data = $q->result();
			
			foreach($data as $k => $v)
			{
				$billing['id'] = $v->id;
				$billing['current'] = $v->current;
				$billing['date'] = $v->date;
				$billing['stmt_period'] = $v->stmt_period;
				$billing['due'] = $v->due_date;
				$billing['thirty'] = $v->ddt;
				$billing['sixty'] = $v->dds;
				$billing['ninety'] = $v->ddn;
				$billing['o_ninety'] = $v->ond;
				$billing['desc'] = $v->description;
				$billing['balance'] = $this->_get_items_balance($v->id);
				$billing['items'] = $this->get_student_billings_items($v->id);
			}			

			return (object)$billing;

		}else{
			return FALSE;
		}
	
	}
	
	private function _get_items_balance($billing_header_id)
	{
		$this->db->select('sum(amount) as total_balance',FALSE)
				 ->where('sbh_id',$billing_header_id)
				 ->where('is_deleted',0);
				 // ->where('ay_id',$this->c_sy->id);
		$q = $this->db->get($this->b_i_t);

		return $q->num_rows() >= 1 ? $q->row()->total_balance == NULL ? 0.00 : $q->row()->total_balance : FALSE;
		 
	}
	
	private function get_student_billings_items($billing_header_id)
	{
		$q = $this->db->select('DATE_FORMAT(date,"%b %e ,%Y") as date,
								  description,
								  amount',FALSE)
				 ->where('sbh_id',$billing_header_id)
				 // ->where('ay_id',$this->c_sy->id)
				 ->where('is_deleted',0)
				 ->get($this->b_i_t);
	
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	private function format_date($date,$from ='m/d/Y',$to='Y-m-d')
	{
		$date_object = DateTime::createFromFormat($from, $date);

		if($date_object !== FALSE)
		{
			return $date_object->format($to);
		}else{
			return NULL;
		}
	}
	
	public function get_fees_for_data($eid,$lid)
	{
		$q = $this->db->select('fee_name,fee_desc,fee_rate,fee_id')
				 ->or_where('is_tuition_fee',1)
				 ->or_where('is_misc_fee',1)
				 ->or_where('is_cca_fee',1)
				 ->or_where('is_elective_fee',1)
				 ->get('fees');
				 
		// vd($q->result());
		if($q->num_rows() >= 1)
		{
			$data[''] = ' - SELECT FEE - ';
			foreach($q->result() as $k => $v)
			{
				$data[$k][$v->fee_name] = $v->fee_name.' | '.$v->fee_desc.' '.$v->fee_rate;
			}
			return $data;
		}else{
			return FALSE;
		}
	}
	
	public function get_specific_billing_form($id = "")
	{
		$q = $this->db->where('id',$id)->limit(1)->get('student_billing_header');
		
		if($q->num_rows() >=1)
		{
			foreach($q->result() as $k => $v)
			{
				$date = DateTime::createFromFormat('Y-m-d',$v->date) == FALSE ? NULL : DateTime::createFromFormat('Y-m-d',$v->date)->format('m/d/Y');
				$due = DateTime::createFromFormat('Y-m-d',$v->due_date) == FALSE ? NULL : DateTime::createFromFormat('Y-m-d',$v->due_date)->format('m/d/Y');
			
				$data['id'] = $v->id;
				$data['current'] = $v->current;
				$data['date'] = $date;
				$data['stmt'] = $v->stmt_period;
				$data['due'] = $due;
				$data['thirty'] = $v->days_due_thirty;
				$data['sixty'] = $v->days_due_sixty;
				$data['ninety'] = $v->days_due_ninety;
				$data['oninety'] = $v->over_ninety_days;
				$data['desc'] = $v->description;
				$data['items'] = $this->_billing_get_items($v->id);
			
				return (object)$data;
			}
			
			// vd($data);
		}else{
			return FALSE;
		}
	}
	
	private function _billing_get_items($id)
	{
		$q = $this->db->where('sbh_id',$id)->get('student_billing_items');
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function _update_billing_form($data,$bid)
	{
		$d = (object)$data;
		// pd($d);
		$date = DateTime::createFromFormat('m/d/Y',$d->date_generated) == FALSE ? NULL : DateTime::createFromFormat('m/d/Y',$d->date_generated)->format('Y-m-d');
		$due = DateTime::createFromFormat('m/d/Y',$d->due_date) == FALSE ? NULL : DateTime::createFromFormat('m/d/Y',$d->due_date)->format('Y-m-d');
		
		//prep for billing header table
		$update_student_billing_header['date'] = $date;
		$update_student_billing_header['stmt_period'] = $d->stmy_period;
		$update_student_billing_header['due_date'] = $due;
		$update_student_billing_header['current'] = $d->current;
		$update_student_billing_header['days_due_thirty'] = $d->thirty_days;
		$update_student_billing_header['days_due_sixty'] = $d->sixty_days;
		$update_student_billing_header['days_due_ninety'] = $d->ninety_days;
		$update_student_billing_header['over_ninety_days'] = $d->overninety_days;
		$update_student_billing_header['description'] = $d->message;

		// for original items
		if($d->items_original)
		{
			foreach($d->items_original as $k => $v)
			{
				$update_student_billing_items[$k]['id'] = $k;
				$update_student_billing_items[$k]['description'] = $v['desc'];
				$update_student_billing_items[$k]['amount'] = $v['price'] * $v['qty'];
				$update_student_billing_items[$k]['qty'] = $v['qty'];
				$update_student_billing_items[$k]['original_amount'] = $v['price'];
				$update_student_billing_items[$k]['updated'] = NOW;
			}
		}else{
			$update_student_billing_items = FALSE;
		}
		
		// for additional items
		foreach($d->items['date'] as $k => $v)
		{
			$date = DateTime::createFromFormat('m/d/Y',$v) == FALSE ? NULL : DateTime::createFromFormat('m/d/Y',$v)->format('Y-m-d');
			$qty = $d->items['qty'][$k] == '' ?  1 : $d->items['qty'][$k];
			
			if($v !== '' AND $d->items['desc'][$k] !== '' AND $d->items['price'][$k] !== '')
			{
				$new_items[$k]['sbh_id'] = $bid;
				$new_items[$k]['date'] = $date;
				$new_items[$k]['description'] = $d->items['desc'][$k];
				$new_items[$k]['amount'] = $d->items['price'][$k] * $qty;
				$new_items[$k]['qty'] = $qty;
				$new_items[$k]['original_amount'] = $d->items['price'][$k];
				$new_items[$k]['created'] = NOW;
			}else{
				
			}
		}
		
		$this->db->trans_start();

		$this->db->set($update_student_billing_header)->where('id',$bid)->update('student_billing_header');
		if($update_student_billing_items)
		{
			$this->db->update_batch('student_billing_items',$update_student_billing_items,'id');
		}
		if($new_items)
		{
			$this->db->insert_batch('student_billing_items',$new_items);
		}
		
		$this->db->trans_complete();
		
		return $this->db->trans_status();
	}
	
	public function get_student_payment_plan($eid)
	{
		$q = $this->db->select('payment_mode')->where('e_id',$eid)->limit(1)->get('enrollments');
		return $q->num_rows() >= 1 ? $q->row()->payment_mode : FALSE;
	}
	
	public function create_student_billing_form($id)
	{
		
		$CI =& get_instance();
		$CI->load->model('M_generate_plans','mgp');
		//get current month
		$current_month = strtolower(date('F'));
		
		//get student payment plan
		$student_pm = $this->get_student_payment_plan($id);
		
		//get months supposed to be paid
		$_months = $CI->mgp->get_months_must_pay($id);
		// $months_m_paid = $CI->mgp->get_student_current_payment_mode_and_due_by_enrollment_id($id);
		// vd($_months);
		//get months unpaid
		if($_months == FALSE)
		{
			return FALSE;
		}else{
			$months_a_paid = $CI->mgp->get_months_paid($id,$_months);
			return $months_a_paid;
		}
	}
	
	public function delete_billing_form($b_id)
	{
		$this->db->where('id',$b_id)->delete('student_billing_header');
		$this->db->where('sbh_id',$b_id)->delete('student_billing_items');
		return TRUE;
	
	}
}